import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

charge_head_router = APIRouter(prefix="/chargeHead", tags=["收费单管理"])


class ChargeBillSearch(BaseModel):
    """收费单查询请求参数"""
    page: int = Field(1, description="页码，默认1")
    limit: int = Field(10, description="每页记录数，默认10，-1表示获取所有数据")
    sort: Optional[str] = Field(None, description="排序字段")
    order: Optional[str] = Field("asc", description="排序方式，默认升序")

    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    minTime: Optional[str] = Field("", description="开始日期(yyyyMMdd)")
    maxTime: Optional[str] = Field("", description="结束日期(yyyyMMdd)")
    fbillno: Optional[str] = Field("", description="单据编号(支持前缀匹配)")
    ftaskno: Optional[str] = Field("", description="任务编号")
    fbillstate: Optional[str] = Field("", description="单据状态")
    fuseuser: Optional[str] = Field("", description="使用人ID")


@charge_head_router.get("/list", summary="收费单查询接口")
def list_chargr_head(search: ChargeBillSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from m_tc_charge_head a,m_ts_task b,a_ts_point c"
    sql = sql + " where 1=1"
    sql = sql + " and a.ftaskno=b.ftaskno"
    sql = sql + " and a.fpoint_no=c.fpoint_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.ftaskno != "":
        sql = sql + " and ftaskno = :ftaskno "
        param["ftaskno"] = search.ftaskno
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if search.fuseuser != "":
        sql = sql + " and fuseuser = :fuseuser "
        param["fuseuser"] = search.fuseuser
    if (search.minTime != "") and (search.maxTime != ""):
        sql = sql + " and to_char(fusedate,'yyyymmdd') between :minTime  and :maxTime "
        param["minTime"] = search.minTime
        param["maxTime"] = search.maxTime
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fbillno,a.fsite_no,a.ftaskno,a.fuseuser,a.fusedate,a.fplanusedate,"
    sql = sql + " a.fpoint_no,a.meetingid,a.fmancount,a.fplanbegtime,a.fplanendtime,"
    sql = sql + " a.frealbegtime,a.frealendtime,a.fplanamt,a.frealamt,a.fexplanation,"
    sql = sql + " a.fupddate,a.foperator,a.fcheckdate,a.fcheckuser,a.fbillstate,"
    sql = sql + " a.fbilltype,b.ftaskname,c.fpoint_name"
    sql = sql + " from m_tc_charge_head a,m_ts_task b,a_ts_point c"
    sql = sql + " where 1=1"
    sql = sql + " and a.ftaskno=b.ftaskno"
    sql = sql + " and a.fpoint_no=c.fpoint_no"
    if search.fsiteNo != "":
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.ftaskno != "":
        sql = sql + " and ftaskno = :ftaskno "
        param["ftaskno"] = search.ftaskno
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if search.fuseuser != "":
        sql = sql + " and fuseuser = :fuseuser "
        param["fuseuser"] = search.fuseuser
    if (search.minTime != "") and (search.maxTime != ""):
        sql = sql + " and to_char(fusedate,'yyyymmdd') between :minTime  and :maxTime "
        param["minTime"] = search.minTime
        param["maxTime"] = search.maxTime
    if search.order == "desc":
        sql = sql + " order by  a.fbillno desc "
    else:
        sql = sql + " order by  a.fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['ftaskno'] = data[key][2]
        d1['fuseuser'] = data[key][3]
        d1['fusedate'] = data[key][4]
        d1['fplanusedate'] = data[key][5]
        d1['fpointno'] = data[key][6]
        d1['meetingid'] = data[key][7]
        d1['fmancount'] = data[key][8]
        d1['fplanbegtime'] = data[key][9]
        d1['fplanendtime'] = data[key][10]
        d1['frealbegtime'] = data[key][11]
        d1['frealendtime'] = data[key][12]
        d1['fplanamt'] = data[key][13]
        d1['frealamt'] = data[key][14]
        d1['fexplanation'] = data[key][15]
        d1['fupddate'] = data[key][16]
        d1['foperator'] = data[key][17]
        d1['fcheckdate'] = data[key][18]
        d1['fcheckuser'] = data[key][19]
        d1['fbillstate'] = data[key][20]
        d1['fbilltype'] = data[key][21]
        d1['ftaskname'] = data[key][22]
        d1['fpointName'] = data[key][23]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
